// // backend/server.js
// import express from 'express'
// import cors from 'cors'
// import mysql from 'mysql2/promise'
// import db from "./db/mysql.js";

// backend/server.js
import express from 'express'
import cors from 'cors'
import mysql from 'mysql2/promise'
import db from "./db/mysql.js";
import fs from 'fs'
import https from 'https'
import dayjs from "dayjs";
import {
    getCommissionDatas,
    getDingtalkAccessToken,
    mergeShopDataWithFill,
    getDateRange,
    formatNumber,
    roundTo2
} from "./dingtalk/utils.js";
import {
    DINGTALK_COMMISSION_TABLE,
    DINGTALK_OPERATOR_ID,
    DINGTALK_APP_KEY,
    DINGTALK_APP_SECRET
} from "./config/dingtalkConfig.js";


const pool = mysql.createPool({
    host: '127.0.0.1',
    user: 'root',
    password: 'root',
    port: "3306",
    database: 'dingding_database',
    waitForConnections: true,
    connectionLimit: 10, // 最大并发连接数
    queueLimit: 0
});


const app = express()
app.use(cors())
app.use(express.json())

// 钉钉 App 配置
const APP_KEY = 'dingyuxv3rvouiwuwpph'
const APP_SECRET = 'NRfqFVWdHdJMw9ro6gKCcakdoSpKK26-ANP6uXUYX_4fCKiJbvOEM1Iayr6SYPvU'
const DING_CORP_ID = 'ding58745046d80bf079ee0f45d8e4f7c288'

// 辅助函数：分钟转小时字符串
function minutesToText(minutes) {
    const hours = Math.floor(minutes / 60);
    const mins = minutes % 60;
    return hours ? `${hours}小时${mins}分钟` : `${mins}分钟`;
}

// ===================== 获取 Access Token =====================
async function getAccessToken() {
    const res = await axios.post('https://api.dingtalk.com/v1.0/oauth2/accessToken', {
        appKey: APP_KEY,
        appSecret: APP_SECRET
    }, {
        headers: {
            'Content-Type': 'application/json'
        }
    })

    if (res.data.accessToken) {
        return res.data.accessToken
    } else {
        throw new Error('获取 accessToken 失败')
    }
}

// ===================== 钉钉登录验证接口 =====================
app.get('/api/dingtalk/getUserInfo', async (req, res) => {
    try {
        const {code} = req.query
        console.log("code:", code)
        const token = await getAccessToken()
        const resp = await fetch(`https://oapi.dingtalk.com/topapi/v2/user/getuserinfo?access_token=${token}`, {
            method: 'POST',
            headers: {'Content-Type': 'application/json'},
            body: JSON.stringify({code})
        })
        const userData = await resp.json()
        const user = userData.result
        const isInternal = user.corp_id === DING_CORP_ID
        res.json({
            success: true,
            userId: user.userid,
            name: user.name,
            isInternal
        })
    } catch (err) {
        res.status(500).json({success: false, message: err.message})
    }
})


// ===================== 钉钉登录验证接口 =====================
app.post('/api/dingtalk/login', async (req, res) => {
    try {
        const {code} = req.body
        const accessToken = await getAccessToken()

        const userInfoRes = await axios.post(
            `https://oapi.dingtalk.com/topapi/v2/user/getuserinfo?access_token=${accessToken}`,
            {code},
            {headers: {'Content-Type': 'application/json'}}
        )

        const data = userInfoRes.data
        if (data.errcode === 0) {
            // 企业内部员工
            res.json({
                success: true,
                userid: data.result.userid,
                name: data.result.name
            })
        } else {
            // 非企业内部员工或错误
            res.json({
                success: false,
                message: data.errmsg || '非企业内部员工'
            })
        }
    } catch (err) {
        console.error(err)
        res.status(500).json({success: false, message: '钉钉验证失败', error: err.message})
    }
})

// ===================== 收入数据接口 =====================


// 1. 获取总收入
app.get('/api/revenue/summary', async (req, res) => {
    try {
        const [rows] = await pool.execute('SELECT SUM(amount) AS total FROM revenues')
        res.json({total: rows[0].total || 0})
    } catch (err) {
        console.error(err)
        res.status(500).json({error: '数据库查询失败'})
    }
})

// 2. 获取收入明细
app.get('/api/revenue/details', async (req, res) => {
    try {
        const [rows] = await pool.execute('SELECT * FROM revenues ORDER BY created_at DESC')
        res.json(rows)
    } catch (err) {
        console.error(err)
        res.status(500).json({error: '数据库查询失败'})
    }
})


// 2. Dashboard 数据
app.post('/api/dashboard/datas', async (req, res) => {
    try {
        // ✅ 1️⃣ 改成从 body 获取参数
        const {nickname, date_or_month, star_parm, end_parm} = req.body;

        /*
            mysql 条件字段：
               douyin_nickname ,date,month
            如果
             nickname 不为空，douyin_nickname不用判断
             如果
             date_or_month == date ，用 date 判断
             date_or_month == month ，用 month 判断
         */
        let sql = `
SELECT douyin_nickname, anchor,
             SUM(daily_gmv) AS sum_gmv,
             SUM(daily_cost) AS sum_cost,
             SUM(daily_gmv) AS sum_daily_gmv,
             SUM(order_subsidy) AS sum_order_subsidy,
             SUM(daily_cost) AS sum_daily_cost,
             SUM(hanging_fee) AS sum_hanging_fee,
             IF((SUM(daily_cost)+SUM(hanging_fee))>0,ROUND((SUM(daily_gmv)-SUM(order_subsidy))/(SUM(daily_cost)+SUM(hanging_fee)),2),0) AS sum_actual_roi,
             SUM(daily_live_mins) AS sum_live_mins
      FROM dd_self_live_summary
      WHERE 1=1
    `;

        // 参数数组
        const params = [];

        // 2️⃣ nickname 条件（不为空才过滤）
        if (nickname) {
            sql += ` AND douyin_nickname = ?`;
            params.push(nickname);
        }

        // 3️⃣ 时间字段选择
        if (date_or_month === "date") {
            sql += ` AND \`date\` BETWEEN ? AND ?`;
            params.push(star_parm, end_parm);
        } else if (date_or_month === "month") {
            sql += ` AND \`month\` BETWEEN ? AND ?`;
            params.push(star_parm, end_parm);
        }

        // 分组排序
        sql += ` GROUP BY douyin_nickname, anchor ORDER BY sum_gmv DESC;`;
        // console.log(sql)
        // console.log(params)
        // ✅ 查询执行
        const zhi_bo_datas = await db.query(sql, params);

        // ✅ 数据分组 + 转换
        const grouped = {};
        zhi_bo_datas.forEach((data) => {
            const douyin_nickname = data.douyin_nickname;
            if (!grouped[douyin_nickname]) grouped[douyin_nickname] = [];

            const sum_live_mins = data.sum_live_mins;
            const sum_live_mins_ch = minutesToText(sum_live_mins);
            data.sum_live_mins_ch = sum_live_mins_ch;
            grouped[douyin_nickname].push(data);
        });

//         const data = grouped;
//         // ✅ 1️⃣ 先对每个店铺内部排序（数组内降序）
//         for (const key in data) {
//             data[key].sort((a, b) => parseFloat(b.sum_gmv) - parseFloat(a.sum_gmv));
//         }
//
// // ✅ 2️⃣ 计算每个店铺的 sum_gmv 总和
//         const totals = Object.entries(data).map(([key, arr]) => {
//             const totalGMV = arr.reduce((sum, item) => sum + parseFloat(item.sum_gmv || 0), 0);
//             return {key, totalGMV};
//         });


        // // ✅ 3️⃣ 按总和降序排序整个对象
        // const sorted = Object.fromEntries(
        //     totals
        //         .sort((a, b) => b.totalGMV - a.totalGMV)
        //         .map(({key}) => [key, data[key]])
        // );


        const tableArrs = [
            "ROOKIE官方旗舰店",
            "Levi‘s李维斯童装集合店",
            "ROOKIE官方旗舰店直播号",
            "ROOKIE官旗书包号",
            "特步儿童户外旗舰店",
            "特步儿童户外运动直播间",
            "翼启飞童鞋专营店",
            "ROOKIE卖场店童装直播间",
            "ROOKIE官旗童装号",
            "anta kids潮流旗舰店"

        ];
        const sorted = {};
        // 按顺序写入
        for (const key of tableArrs) {
            sorted[key] = grouped[key] ?? []; // 如果不存在，则写入空数组
        }

        res.json(sorted);
    } catch (err) {
        console.error(err);
        res.status(500).json({error: "数据库查询失败"});
    }
});

app.post('/api/dashboard/shop_month_datas', async (req, res) => {
    try {

        let sql = `
        SELECT douyin_nickname ,
        SUM(daily_gmv) AS sum_month_gmv,
        SUM(daily_cost) AS sum_month_cost,
        IF(SUM(daily_cost)>0,ROUND(SUM(daily_gmv)/SUM(daily_cost),2),0) AS month_roi 
         FROM dd_self_live_summary WHERE \`month\`=CONCAT(YEAR(CURDATE()),"-",MONTH(CURDATE())) GROUP BY douyin_nickname;
    `;


        // ✅ 查询执行
        const zhi_bo_datas = await db.query(sql);

        // ✅ 数据分组 + 转换
        const grouped = {};
        zhi_bo_datas.forEach((data) => {
            const douyin_nickname = data.douyin_nickname;
            grouped[douyin_nickname] = data;
        });

        res.json(grouped);
    } catch (err) {
        console.error(err);
        res.status(500).json({error: "数据库查询失败"});
    }
});


/*抖音项目分析首页数据*/
import {
    getZongKanBanDatas, getZhengTiChengJiaoLineDatasRes, getShopDatas
} from "./dingtalk/DouyinProjectAnalysisIndexDatas.js";


app.post('/api/douyin_project_analysis/index_datas', async (req, res) => {
    try {

        // ✅ 1️⃣ 改成从 body 获取参数
        const {date_or_month, star_parm, end_parm} = req.body;
        console.log(`date_or_month:${date_or_month}   star_parm:${star_parm}  end_parm:${end_parm}`)
        const allDates = await getDateRange(star_parm, end_parm);
        const sql_params = [];
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        /*抖音项目分析总看板 sql  数据 */
        const zong_kan_ban_datas_res = await getZongKanBanDatas(date_or_month, star_parm, end_parm)


        /*整体成交_折线图  sql + datas*/
        let zheng_ti_cheng_jiao_line_datas_res = await getZhengTiChengJiaoLineDatasRes(date_or_month, star_parm, end_parm, allDates)


        /*整体成交_每个店铺指标数据 sql + datas */
        let shop_datas = await getShopDatas(date_or_month, star_parm, end_parm, allDates)


        res.json({
            "zong_kan_ban_datas": zong_kan_ban_datas_res,
            "zheng_ti_cheng_jiao_line_datas": zheng_ti_cheng_jiao_line_datas_res,
            "shop_datas": shop_datas
        });


    } catch (err) {
        console.error(err);
        res.status(500).json({error: "数据库查询失败"});
    }
});


app.post('/api/douyin_project_analysis/shop_self_coop_detail', async (req, res) => {

    let res_data = []

    try {

        const {date_or_month, star_parm, end_parm, shop_name, is_self, type} = req.body;
        const allDates = await getDateRange(star_parm, end_parm);

        let sql = ``
        if (date_or_month === "date") {
            sql = `SELECT account_or_channel, DATE_FORMAT(date, '%Y-%m-%d') AS date,sum(user_payment_amount) as user_payment_amount,
sum(refund_amount_payment_time) as refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND date BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=?  GROUP BY  account_or_channel;`
        } else {
            sql = `SELECT account_or_channel, DATE_FORMAT(date, '%Y-%m-%d') AS date,sum(user_payment_amount) as user_payment_amount,
sum(refund_amount_payment_time) as refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND month_str BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=?  GROUP BY  account_or_channel;`
        }

        const sql_params = [];
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(shop_name);
        if (is_self === "self") {
            sql_params.push("自营");
        } else {
            sql_params.push("带货");
        }
        sql_params.push(type);
        const sql_datas = await db.query(sql, sql_params);

        let user_pay_total = 0;  //支付总金额
        let refund_amount_payment_total = 0; //退款总金额
        let refund_rate_total = 0; //总的退款比例

        let account_data_json = {}

        for (const data of sql_datas) {

            let account_or_channel = data["account_or_channel"]
            if (!(account_or_channel in account_data_json)) {
                account_data_json[account_or_channel] = {};
                account_data_json[account_or_channel]["pay_total"] = 0; //抖音号对应 总金额
                account_data_json[account_or_channel]["pay_total_rate"] = 0 //抖音号对应 总金额比例
                account_data_json[account_or_channel]["refund_total"] = 0 //抖音号对应 退款金额
                account_data_json[account_or_channel]["refund_total_date"] = 0 //抖音号对应 退款比例
            }

            let user_payment_amount = data["user_payment_amount"]
            user_pay_total += parseFloat(user_payment_amount);
            account_data_json[account_or_channel]["pay_total"] += parseFloat(user_payment_amount);
            let refund_amount_payment_time = data["refund_amount_payment_time"]
            refund_amount_payment_total += parseFloat(refund_amount_payment_time);
            account_data_json[account_or_channel]["refund_total"] += parseFloat(refund_amount_payment_time)
            let date_str = data["date"]
        }

        for (const account in account_data_json) {
            //用户支付金额 占比
            let pay_total = await roundTo2(account_data_json[account]["pay_total"]);
            let pay_total_rate = 0
            if (user_pay_total > 0) {
                pay_total_rate = (await roundTo2(pay_total * 100 / user_pay_total))
            }

            //用户退款金额 占比
            let refund_total = await roundTo2(account_data_json[account]["refund_total"]);
            let refund_total_date = 0;
            if (refund_amount_payment_total > 0) {
                refund_total_date = (await roundTo2(refund_total * 100 / refund_amount_payment_total))
            }


            let res_data_item = {
                "抖音昵称": account, "支付金额": pay_total, "支付比例": pay_total_rate + "%",
                "退款金额": refund_total, "退款比例": refund_total_date + "%"
            }
            res_data.push(res_data_item);
        }

        res.json({
            "data": res_data
        });


    } catch (err) {
        console.error(err);
        res.status(500).json({error: "查询失败"});
    }
});



app.post('/api/douyin_project_analysis/table_and_tand_line', async (req, res) => {

    let res_data = []

    try {

        const {date_or_month, star_parm, end_parm, shop_name, is_self, type, account_or_channel} = req.body;


        console.log(date_or_month, star_parm, end_parm, shop_name, is_self, type, account_or_channel)

        const allDates = await getDateRange(star_parm, end_parm);

        let sql = ``
        if (date_or_month === "date") {
            sql = `SELECT account_or_channel, DATE_FORMAT(date, '%Y-%m-%d') AS date,user_payment_amount,refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND date BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=? and account_or_channel=? order by date desc ;`
        } else {
            sql = `SELECT account_or_channel, DATE_FORMAT(date, '%Y-%m-%d') AS date,user_payment_amount,refund_amount_payment_time FROM dd_carrier_composition_datas 
WHERE 1=1 AND month_str BETWEEN ? AND ? AND shop_name=? AND is_self=? AND type=? and account_or_channel=? order by date desc ;`
        }

        const sql_params = [];
        sql_params.push(star_parm);
        sql_params.push(end_parm);
        sql_params.push(shop_name);
        if (is_self === "self") {
            sql_params.push("自营");
        } else {
            sql_params.push("带货");
        }
        sql_params.push(type);
        sql_params.push(account_or_channel);
        const sql_datas = await db.query(sql, sql_params);

        // { 日期: "2025-10-01", 支付金额: 8646.54, 支付比例: "14.11%", 退款金额: 4704.0, 退款比例: "51.54%" },

        let user_pay_total = 0;  //支付总金额
        let refund_amount_payment_total = 0; //退款总金额
        let refund_rate_total = 0; //总的退款比例


        let table_detail_json = {}

        for (const data of sql_datas) {
            let user_payment_amount = data["user_payment_amount"]
            user_pay_total += parseFloat(user_payment_amount);
            let refund_amount_payment_time = data["refund_amount_payment_time"]
            refund_amount_payment_total += parseFloat(refund_amount_payment_time);
            let date_str = data["date"]
            table_detail_json[date_str] = data
        }

        const tableData = []
        const trendLines = {}
        trendLines["days"] = []
        trendLines["pay"] = []
        trendLines["refund"] = []
        for (const day of allDates) {
            trendLines["days"].push(day);
            let user_payment_amount = 0;
            let refund_amount_payment = 0;
            //    { 日期: "2025-10-01", 支付金额: 8646.54, 支付比例: "14.11%", 退款金额: 4704.0, 退款比例: "51.54%" },
            if (day in table_detail_json) {

                const itemData = table_detail_json[day]
                user_payment_amount = itemData["user_payment_amount"]
                let user_payment_amount_rate = 0;
                if (user_pay_total > 0) {
                    user_payment_amount_rate = await roundTo2(user_payment_amount / user_pay_total)
                    console.log("user_payment_amount:",user_payment_amount,"  ","user_pay_total",user_pay_total,"  user_payment_amount_rate:",user_payment_amount_rate)
                }
                refund_amount_payment = itemData["refund_amount_payment_time"]
                let refund_amount_payment_rate = 0;
                if (refund_amount_payment_total > 0) {
                    refund_amount_payment_rate = await roundTo2(refund_amount_payment / refund_amount_payment_total)
                    console.log("refund_amount_payment:",refund_amount_payment,"  ",
                        "refund_amount_payment_total",refund_amount_payment_total,"  refund_amount_payment_rate:",refund_amount_payment_rate)
                }

                let tableDataItem = {
                    "日期": itemData["date"],
                    "支付金额": user_payment_amount, "支付比例": user_payment_amount_rate + "%",
                    "退款金额": refund_amount_payment, "退款比例": refund_amount_payment_rate + "%"
                }
                tableData.push(tableDataItem);
            }

            trendLines["pay"].push(user_payment_amount);
            trendLines["refund"].push(refund_amount_payment);
        }


        res.json({
            "tableData": tableData,
            "trendLines": trendLines
        });


    } catch (err) {
        console.error(err);
        res.status(500).json({error: "查询失败"});
    }
});


// ✅ HTTPS 证书加载
const options = {
    key: fs.readFileSync('./ssl/dingding.link-hope.cn251112.key'),
    cert: fs.readFileSync('./ssl/dingding.link-hope.cn251112.pem')
}

app.listen(3000, () => {
    console.log('Server running on http://localhost:3000')
})

// // 启动 HTTPS 服务
// https.createServer(options, app).listen(3000, '0.0.0.0', () => {
//     console.log('✅ HTTPS Server running at https://dingding.link-hope.cn:3000')
// })